USE CAFEDB
GO
-----------------------------GET PROCEDURE --------------------------------
--AREA
CREATE PROC GET_AREA_ALL
AS
	BEGIN
		SELECT [AREAID],[AREANAME]FROM [CAFEDB].[dbo].[AREA]
	END
GO
--DEPARTMENT
CREATE PROC GET_DEPARTMENT_ALL
AS
	BEGIN
		SELECT [DEPARTMENTID]
      ,[DEPARTMENTNAME]
  FROM [CAFEDB].[dbo].[DEPARTMENT]
	END
GO
--DETAIL_GROUPUSER_ROLE
CREATE PROC GET_DETAILGROUPUSERROLE_ALL
AS
	BEGIN
		SELECT [GROUPUSERCODE]
      ,[ROLECODE]
  FROM [CAFEDB].[dbo].[DETAIL_GROUPUSER_ROLE]
	END
GO
--EMPLOYER
CREATE PROC GET_EMPLOYER_ALL
AS
	BEGIN
		SELECT [EMPLOYERID]
      ,[FIRSTNAME]
      ,[MIDDLENAME]
      ,[LASTNAME]
      ,[FULLNAME]
      ,[DOB]
      ,[IDCARD]
      ,[ADDRESS]
      ,[DATEWORK]
      ,[DEPARTMENT].[DEPARTMENTID],DEPARTMENTNAME
  FROM [CAFEDB].[dbo].[EMPLOYER],[CAFEDB].[dbo].[DEPARTMENT]
  WHERE [DEPARTMENT].DEPARTMENTID=EMPLOYER.DEPARTMENTID
	END
GO
--EXPORT
CREATE PROC GET_EXPORT_ALL
AS
	BEGIN
		SELECT [EXPORTID]
      ,[TABLEID]
      ,[EMPLOYERID]
      ,[TABLEDISCOUNT]
  FROM [CAFEDB].[dbo].[EXPORT]
	END
GO
--EXPORTDETAIL
CREATE PROC GET_EXPORTDETAIL_ALL
AS
	BEGIN
		SELECT [EXPORTID]
      ,[ITEMID]
      ,[QUANTITY]
      ,[PRICE]
      ,[ITEMDISCOUNT]
  FROM [CAFEDB].[dbo].[EXPORTDETAIL]
	END
GO
--GROUPUSER
CREATE PROC GET_GROUPUSER_ALL
AS
	BEGIN
		SELECT [GROUPUSERCODE]
      ,[GROUPUSERNAME]
      ,[GROUPUSERDESCRIPTION]
      ,[USERCREATE]
      ,[DATECREATE]
      ,[USERUPDATE]
      ,[LASTUPDATE]
  FROM [CAFEDB].[dbo].[GROUPUSER]
	END
GO
--IMPORT
CREATE PROC GET_IMPORT_ALL
AS
	BEGIN
		SELECT [IMPORTID]
      ,[DATEIMPORT]
      ,[ISDEBT]
      ,[PAID]
      ,[NOTE]
      ,[EMPLOYERID]
  FROM [CAFEDB].[dbo].[IMPORT]
	END
GO
--IMPORTDETAIL
CREATE PROC GET_IMPORTDETAIL_ALL
AS
	BEGIN
		SELECT [IMPORTID]
      ,[MATERIALID]
      ,[QUANTITY]
      ,[UNITID]
  FROM [CAFEDB].[dbo].[IMPORTDETAIL]
	END
GO
--ITEM
CREATE  PROC GET_ITEM_ALL
AS
	BEGIN
		SELECT [ITEMID]
      ,[ITEMNAME]
      ,ITEM.[ITEMTYPEID]
      ,[ISDRINK]
      ,[PRICE]
      ,[SHORTCUT]
      ,ITEMTYPENAME
      
  FROM [CAFEDB].[dbo].[ITEM],[CAFEDB].[dbo].[ITEMTYPE]
  WHERE ITEM.ITEMTYPEID=ITEMTYPE.ITEMTYPEID
	END
GO
--ITEMTYPE
CREATE PROC GET_ITEMTYPE_ALL
AS
	BEGIN
		SELECT [ITEMTYPEID]
      ,[ITEMTYPENAME]
  FROM [CAFEDB].[dbo].[ITEMTYPE]
	END
GO
--MATERIAL
CREATE PROC GET_MATERIAL_ALL
AS
	BEGIN
		SELECT [MATERIALID]
      ,[MATERIALNAME]
      ,[QUANTITY]
      ,[UNIT].[UNITID]
      ,[UNITNAME]
  FROM [CAFEDB].[dbo].[MATERIAL]
		,[CAFEDB].[dbo].[UNIT]
  WHERE UNIT.UNITID=MATERIAL.UNITID
	END
GO
--GROUP ROLE
CREATE PROC GET_GROUPROLE_ALL
AS
	BEGIN
		SELECT [GROUPROLECODE]
      ,GROUPROLENAME
      ,[USERCREATE]
      ,[DATECREATE]
      ,[USERUPDATE]
      ,[LASTUPDATE]
  FROM [CAFEDB].[dbo].[GROUPROLE]
	END
GO
--ROLE
CREATE  PROC GET_ROLE_ALL
AS
	BEGIN
		SELECT [ROLECODE]
      ,[ROLENAME]
      ,[ROLE].[GROUPROLECODE]
      ,[ROLE].[USERCREATE]
      ,[ROLE].[DATECREATE]
      ,[ROLE].[USERUPDATE]
      ,[ROLE].[LASTUPDATE]
      ,[GROUPROLENAME]
  FROM [CAFEDB].[dbo].[ROLE],[CAFEDB].[dbo].[GROUPROLE]
	END
GO
--GET ROLE FOR PERMITSION
CREATE   PROC GET_ROLE_PERMITSION(@GROUPUSERCODE INT)
AS

	BEGIN
		SELECT [ROLECODE]
      ,[ROLENAME]
      ,[ROLE].[GROUPROLECODE]
      ,[ROLE].[USERCREATE]
      ,[ROLE].[DATECREATE]
      ,[ROLE].[USERUPDATE]
      ,[ROLE].[LASTUPDATE]
      ,[GROUPROLENAME]
      ,CASE WHEN ROLECODE in (SELECT [ROLE].ROLECODE FROM DETAIL_GROUPUSER_ROLE,[ROLE] WHERE DETAIL_GROUPUSER_ROLE.GROUPUSERCODE= @GROUPUSERCODE and [ROLE].ROLECODE=DETAIL_GROUPUSER_ROLE.ROLECODE) THEN 'true' ELSE 'false' END AS ISCHECK
  FROM [CAFEDB].[dbo].[ROLE],[CAFEDB].[dbo].[GROUPROLE]
  WHERE [CAFEDB].[dbo].[ROLE].GROUPROLECODE=[CAFEDB].[dbo].[GROUPROLE].GROUPROLECODE
	END
GO

--TABLE
CREATE  PROC GET_TABLE_ALL
AS
	BEGIN
		SELECT [TABLEID]
      ,[TABLENAME]
      ,[TABLE].[AREAID]
      ,[AREA].[AREANAME]
  FROM [CAFEDB].[dbo].[TABLE]
		,[CAFEDB].[dbo].[AREA]
  WHERE [CAFEDB].[dbo].[TABLE].[AREAID]=[CAFEDB].[dbo].[AREA].[AREAID]
	END
GO
--UNIT
CREATE PROC GET_UNIT_ALL
AS
	BEGIN
		SELECT [UNITID]
      ,[UNITNAME]
  FROM [CAFEDB].[dbo].[UNIT]
	END
GO
--USERACCOUNT
CREATE  PROC GET_USERACCOUNT_ALL
AS
	BEGIN
		SELECT [USERNAME]
      ,[PASSWORD]
      ,USERACCOUNT.[FIRSTNAME]
      ,USERACCOUNT.[MIDDLENAME]
      ,USERACCOUNT.[LASTNAME]
      ,USERACCOUNT.[FULLNAME]
      ,USERACCOUNT.[DOB]
      ,[EMAIL]
      ,USERACCOUNT.[IDCARD]
      ,[DATECREATE]
      ,[LASTUPDATE]
      ,[ISADMIN]
      ,[MOBILEPHONE]
      ,[HOMEPHONE]
      ,[GROUPUSERCODE]
      ,USERACCOUNT.[EMPLOYERID]
      ,EMPLOYER.FULLNAME
  FROM [CAFEDB].[dbo].[USERACCOUNT],EMPLOYER
  WHERE USERACCOUNT.EMPLOYERID=EMPLOYER.EMPLOYERID
	END
GO

CREATE  PROC LOGIN_CHECK(@USERNAME varchar(30), @PASSWORD varchar(32))
AS
	BEGIN
		SELECT [USERNAME]
      ,[PASSWORD]
      ,USERACCOUNT.[FIRSTNAME]
      ,USERACCOUNT.[MIDDLENAME]
      ,USERACCOUNT.[LASTNAME]
      ,USERACCOUNT.[FULLNAME]
      ,USERACCOUNT.[DOB]
      ,[EMAIL]
      ,USERACCOUNT.[IDCARD]
      ,[DATECREATE]
      ,[LASTUPDATE]
      ,[ISADMIN]
      ,[MOBILEPHONE]
      ,[HOMEPHONE]
      ,[GROUPUSERCODE]
      ,USERACCOUNT.[EMPLOYERID]
      ,EMPLOYER.FULLNAME
  FROM [CAFEDB].[dbo].[USERACCOUNT],EMPLOYER
  WHERE USERACCOUNT.EMPLOYERID=EMPLOYER.EMPLOYERID
  AND USERACCOUNT.USERNAME=@USERNAME
  AND USERACCOUNT.PASSWORD=@PASSWORD
	END
GO
--WAGE
CREATE PROC GET_WAGE_ALL
AS
	BEGIN
		SELECT [WAGEID]
      ,[WAGENAME]
      ,[PRICE]
      ,[WAGE].[DEPARTMENTID]
      ,DEPARTMENTNAME
  FROM [CAFEDB].[dbo].[WAGE],[CAFEDB].[dbo].[DEPARTMENT]
WHERE WAGE.DEPARTMENTID=DEPARTMENT.DEPARTMENTID

	END
GO

 